-- Package que implementa la api de los codigos de prepago en la base de datos.
CREATE OR REPLACE PACKAGE BODY "PPC_PREPAID_PK" AS

    lMagicKey   varchar2(8) := 'd1m4c0f1';

    -- codigos internos
    g_d_USED    VARCHAR2(1) := 'S';
    g_d_ACTIVE  VARCHAR2(1) := 'C';
    g_d_VOID    VARCHAR2(1) := 'O';
    g_d_EXPIRED VARCHAR2(1) := 'X';

    function encrypt (p_Code IN VARCHAR2) RETURN VARCHAR2
    is
        lData           VARCHAR2(200);
        lEncriptedCode  VARCHAR2(200);
    begin
        lData := rpad( p_Code, ( trunc( length( p_Code ) / 8 ) + 1 ) * 8, chr( 0 ) );
        dbms_obfuscation_toolkit.DESEncrypt( input_string       => lData,
                                             key_string         => lMagicKey,
                                             encrypted_string   => lEncriptedCode );
        return lEncriptedCode;
    end;

    function decript (p_Code IN VARCHAR2) RETURN VARCHAR2
    is
        lData           VARCHAR2(200);
        lDecriptedCode  VARCHAR2(200);
    begin
        dbms_obfuscation_toolkit.DESDecrypt( input_string       =>p_Code,
                                             key_string         =>lMagicKey,
                                             decrypted_string   =>lData );
        lDecriptedCode := rtrim( lData, chr(0) );
        return lDecriptedCode;
    end;

    function createCode (   p_value                 NUMBER,
                            p_source_object_id      in number,
                            p_source_object_code    in varchar2,
                            p_expiration_months     in number
                        )  return varchar2
    is
        lIntCode        VARCHAR2(20);
        lEncriptedCode  VARCHAR2(200);
        nextId          number;
    begin
        --
        -- Busco el codigo
        select  dbms_random.string('x',12)
        into    lIntCode
        from    dual;

        -- Busco el Id de la secuencia
        select pos_prepaid_codes_seq.nextval into nextId from dual;

        -- Lo encrypto
        lEncriptedCode := encrypt( upper( lIntCode ) );

        -- Lo inserto en la tabla
        insert into pos_prepaid_code ( id,
                                       code,
                                       status,
                                       exp_date,
                                       value,
                                       source_object_id,
                                       source_object_code,
                                       creation_date
                                    )
        values ( nextId
                ,lEncriptedCode
                ,g_d_ACTIVE
                ,add_months( sysdate, p_expiration_months )
                ,p_value
                ,p_source_object_id
                ,p_source_object_code
                ,sysdate);
        -- Si no dio error devuelvo el codigo
        return lIntCode;
    exception when others then
        -- si ya existe devuelvo como duplicado
        return 'DUPLICATE';
    end;


    procedure create_code (p_value                 in varchar2,
                        r_code                  out nocopy varchar2,
                        p_source_object_id      in VARCHAR2,
                        p_source_object_code    in VARCHAR2,
                        p_expiration_months     in number
                        )
    is
        -- Variables privadas
        lCode   VARCHAR2(20) := 'DUPLICATE';
        lCount  NUMBER := 1;
        pragma  autonomous_transaction;
    BEGIN

        -- llamo a la funcion interna mientras no me de error
        while lCode = 'DUPLICATE'
        loop
            lCode := createCode(    p_value,
                                    p_source_object_id,
                                    p_source_object_code,
                                    p_expiration_months
                                );

            -- Por si acaso cuento.. y si me voy mas de 1.000.000 es por que fallo
            if lCount >= 100000 then
                r_code :=  'ERROR';
                rollback;
                return;
            else
                lCount := lCount +1;
            end if;
        end loop;

        -- devuelvo el codigo
        commit;
        r_code := lCode;
    END;

    procedure get_code_with_source (r_code                  out nocopy varchar2,
                                    p_source_object_id      in VARCHAR2,
                                    p_source_object_code    in VARCHAR2
                                    )
    is
        lDbCOde         VARCHAR2(200);
    BEGIN
        r_code := null;
        lDbCOde := null;
        -- verifico si existe el codigo
        select code
        into   lDbCOde
        from   pos_prepaid_code
        where  source_object_id   = p_source_object_id
        and    source_object_code = p_source_object_code;

        if lDbCOde is not null then
            r_code := decript( lDbCOde );
        end if;
    END;


    procedure get_Status (  p_code  in  varchar2,
                            r_value out nocopy varchar2,
                            r_valid out nocopy varchar2,
                            r_id    out nocopy varchar2)
    is
        cursor c_code ( p_code in varchar2 ) is
        select  rowid,
                id,
                code,
                status,
                exp_date,
                value
        from    pos_prepaid_code
        where   code = p_code;

        x_value         NUMBER;
        x_valid         VARCHAR2(20);
        x_id            VARCHAR2(20);

        lEncRec         c_code%ROWTYPE;
        lEncriptedCode  VARCHAR2(200);
    BEGIN
        -- Lo encrypto
        lEncriptedCode := encrypt( upper ( p_Code ) );

        -- busco el codigo
        OPEN c_code ( lEncriptedCode );
        fetch c_code into lEncRec;

        if c_code%found then
            x_value := lEncRec.value;
            x_id := lEncRec.id;

            if sysdate > lEncRec.exp_Date then
                update pos_prepaid_code
                set    status = g_d_expired
                where   rowid = lEncRec.rowid;
                        x_value := 0;
                        x_valid :=  g_Expired;
            elsif lEncRec.status = g_d_USED then
                x_valid := g_Used;
                x_value := 0;
            elsif lEncRec.status = g_d_VOID then
                x_valid := g_VOID;
                x_value := 0;
            else
                x_valid := g_ACTIVE;
            end if;
        else
            x_valid := g_DNE;
        end if;
        close c_code;
        r_value := x_value;
        r_valid := x_valid;
        r_id    := x_id;
    end;


    procedure consume ( p_code                      IN VARCHAR2,
                        p_destination_object_id     in number,
                        p_destination_object_code   in varchar2,
                        r_retval                    out number
                        )
    is
        lEncriptedCode  VARCHAR2(200);
        lDecriptedCode  VARCHAR2(200);
        lDbCOde         VARCHAR2(200);
        lDummy          NUMBER;
    begin

        -- Lo encrypto
        lEncriptedCode := encrypt( upper( p_Code ) );

        -- Me fijo si hace falta expirar el codigo
        update pos_prepaid_code
        set    status = g_d_expired
        where  code = lEncriptedCode
        and    status = g_d_ACTIVE
        and    sysdate > exp_date;

        begin
            -- verifico si existe el codigo y esta activo
            select 1
            into   r_retval
            from   pos_prepaid_code
            where  code = lEncriptedCode
            and    status = g_d_ACTIVE;
            
            EXCEPTION
               WHEN NO_DATA_FOUND THEN
                   r_retval := 0;
        end;

        -- Lo actualizo
        if r_retval = 1 then
            update pos_prepaid_code
            set status = g_d_USED,
                used_date = sysdate,
                destination_object_id = p_destination_object_id,
                destination_object_code = p_destination_object_code
            where code = lEncriptedCode;
        end if;
    end;


    procedure void_with_source( p_source_object_id   IN number,
                                p_source_object_code IN varchar2
                                )
    is
        lEncriptedCode  VARCHAR2(200);
        lDecriptedCode  VARCHAR2(200);
        lDbCOde         VARCHAR2(1);
        l_dummy         NUMBER;
    begin
        l_dummy:=0;
        -- verifico si existe el codigo
        select 1
        into   l_Dummy
        from   pos_prepaid_code
        where  source_object_id   = p_source_object_id
        and    source_object_code = p_source_object_code;

        if l_Dummy = 1 then
            -- Lo actualizo
            update pos_prepaid_code
            set    status = g_d_void
            where  source_object_id = p_source_object_id
            and    source_object_code = p_source_object_code;
        end if;
    end;


    procedure void_with_code( p_code IN VARCHAR2 )
    is
        lEncriptedCode  VARCHAR2(200);
        lDecriptedCode  VARCHAR2(200);
        lDbCOde         VARCHAR2(1);
        l_Dummy         NUMBER;
    begin
        l_dummy := 0;
        lEncriptedCode := encrypt(upper(p_Code));
        -- verifico si existe el codigo
        select 1
        into   l_Dummy
        from   pos_prepaid_code
        where  code = lEncriptedCode;

        if l_Dummy = 1 then
            -- Lo actualizo
            update pos_prepaid_code
            set    status = g_d_void
            where  code = lEncriptedCode;
        end if;
    end;


    procedure drop_with_code ( p_code IN VARCHAR2 )
    is
        lEncriptedCode  VARCHAR2(200);
        lDecriptedCode  VARCHAR2(200);
        lDbCode         VARCHAR2(2000);
    begin
        -- Lo encrypto
        lEncriptedCode := encrypt( upper( p_code ) );

        select code
        into   lDbCOde
        from   pos_prepaid_code
        where  code = lEncriptedCode;

        lDecriptedCode := decript( lDbCOde );
        if upper( lDecriptedCode ) = upper( p_code )
        then
            delete from pos_prepaid_code
            where  code = lEncriptedCode;
        end if;
    end;


    procedure drop_with_source( p_source_object_id      IN VARCHAR2,
                                p_source_object_code    IN VARCHAR2
                            )
    is
        lEncriptedCode  VARCHAR2(200);
        lDecriptedCode  VARCHAR2(200);
        lDbCode         VARCHAR2(2000);
    begin
        select code
        into   lDbCOde
        from   pos_prepaid_code
        where  source_object_id = p_source_object_id
        and    source_object_code = p_source_object_code;

        lDecriptedCode := decript( lDbCOde );
        delete from pos_prepaid_code
        where  source_object_id = p_source_object_id
        and    source_object_code = p_source_object_code;
    end;

end;
/
